﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OracleClient;
using DreamBuilder.Common;
using DreamBuilder.Core.DAO;
using System.Data.Common;
using CommonModule;
using Web.SystemConfig.SysCache;
using Web.SystemConfig.Models;
using Web.SystemConfig.Business;

namespace Web.SystemConfig.WorkFlow
{
    public class WorkFlowDataAccess : DataHelper
    {
        /// <summary>
        /// 添加录入日志
        /// </summary>
        /// <param name="tableID"></param>
        /// <param name="recordID"></param>
        /// <param name="operateID"></param>
        /// <param name="commentMessage"></param>
        /// <param name="fromUserID"></param>
        /// <param name="fromStatusID"></param>
        /// <param name="toUserID"></param>
        /// <param name="toStatusID"></param>
        /// <param name="IsLast"></param>
        /// <returns></returns>
        public string AddInsertLog(int tableID, Guid recordID, int operateID, string commentMessage, Guid fromUserID, int fromStatusID, Guid toUserID, int toStatusID, string signUserID, int IsLast)
        {
            string strSql = string.Empty;
            string rtnStr = string.Empty;
            try
            {
                if (DBHelper.database is Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase)
                {
                    strSql = string.Format(@"insert into workflow_operatelog values ('{0}',sysdate,'{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}',sysdate,'{10}')",
                                                                                            Guid.NewGuid(),
                                                                                            tableID,
                                                                                            recordID,
                                                                                            operateID,
                                                                                            commentMessage,
                                                                                            fromStatusID,
                                                                                            toStatusID,
                                                                                            fromUserID,
                                                                                            toUserID,
                                                                                            signUserID,
                                                                                            IsLast);
                    DBHelper.ExecuteSql(strSql);
                }
                else
                {
                    throw new Exception("此系统只支持oracle");
                }
            }
            catch (Exception ex)
            {
                rtnStr = ex.Message;
            }
            return rtnStr;
        }

        /// <summary>
        /// 新增操作日志
        /// </summary>
        /// <param name="tableID"></param>
        /// <param name="recordID"></param>
        /// <param name="operateID"></param>
        /// <param name="commentMessage"></param>
        /// <param name="fromUserID"></param>
        /// <param name="fromStatusID"></param>
        /// <param name="toUserID"></param>
        /// <param name="toStatusID"></param>
        /// <returns></returns>
        public string AddNewFlowLog(int tableID, Guid recordID, int operateID, string commentMessage, Guid fromUserID, int fromStatusID, Guid toUserID, int toStatusID, bool NeedUpdateStatus)
        {
            string rtnStr = string.Empty;
            try
            {
                if (DBHelper.database is Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase)
                {
                    if (NeedUpdateStatus)
                    {
                        string strSetIslast = string.Format("update workflow_operatelog set islast=0 where tableid='{0}' and recordid='{1}'", tableID, recordID);
                        DBHelper.ExecuteSql(strSetIslast);
                    }
                    string strSql = string.Format(@"insert into workflow_operatelog values ('{0}',sysdate,'{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}',null,null,'1')",
                                                                                            Guid.NewGuid(),
                                                                                            tableID,
                                                                                            recordID,
                                                                                            operateID,
                                                                                            commentMessage,
                                                                                            fromStatusID,
                                                                                            toStatusID,
                                                                                            fromUserID,
                                                                                            toUserID);
                    DBHelper.ExecuteSql(strSql);
                }
                else
                {
                    throw new Exception("此系统只支持oracle");
                }
            }
            catch (Exception ex)
            {
                rtnStr = ex.Message;
            }
            return rtnStr;
        }

        /// <summary>
        /// 驱动流程到下一阶段
        /// </summary>
        /// <param name="tableID"></param>
        /// <param name="recordID"></param>
        /// <returns></returns>
        public string ProcessFlowLog(Guid operatelogID)
        {
            DbParameter[] arrParam;
            string strSql = string.Empty;
            string rtnStr = string.Empty;
            try
            {
                if (DBHelper.database is Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase)
                {
                    arrParam = new DbParameter[] { 
                                                                    new OracleParameter(":OperatelogID",operatelogID)
                                                                 };
                    strSql = "update WORKFLOWOPERATELOG set signuserid=:signuserid and signtime=sysdate() where operatelogID=:OperatelogID";
                    DBHelper.ExecuteSql(strSql, arrParam);
                }
                else
                {
                    throw new Exception("此系统只支持oracle");
                }
            }
            catch (Exception ex)
            {
                rtnStr = ex.Message;
            }
            return rtnStr;
        }

        /// <summary>
        /// 驱动业务表状态
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="recordID"></param>
        /// <param name="statusID"></param>
        public void ProcessStatus(string tableName, string recordID, int statusID)
        {
            if (DBHelper.database is Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase)
            {
                string sql = string.Format("update {0} set statusID={1} where DataID='{2}'", tableName, statusID, recordID);
                this.DBHelper.ExecuteSql(sql);
            }
            else
            {
                throw new Exception("此系统只支持oracle");
            }
        }

        /// <summary>
        /// 本人自己关闭流程
        /// </summary>
        /// <param name="tableID"></param>
        /// <param name="recordID"></param>
        public void FinishFlowLog(int tableID, string recordID, Guid toUserID)
        {
            string strSql = string.Empty;
            try
            {
                if (DBHelper.database is Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase)
                {
                    strSql = string.Format(@"declare IsLastCount number;begin select count(*) into IsLastCount from workflow_operatelog where tableid='{0}' and recordid='{1}' and touserID='{2}' and islast=1;if IsLastCount=1 then update workflow_operatelog set IsLast=0 where tableid='{0}' and recordid='{1}' and islast=1;end if;end;", tableID, recordID, toUserID);
                    DBHelper.ExecuteScalar(strSql);
                }
                else
                {
                    throw new Exception("此系统只支持oracle");
                }
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// 直接系统关闭流程
        /// </summary>
        /// <param name="tableID"></param>
        /// <param name="recordID"></param>
        public void FinishFlowLog(int tableID, string recordID)
        {
            string strSql = string.Empty;
            try
            {
                if (DBHelper.database is Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase)
                {
                    strSql = string.Format(@"declare IsLastCount number;
                                                            begin
                                                                    select count(*) into IsLastCount from workflow_operatelog where tableid='{0}' and recordid='{1}'  and islast=1;
                                                                    if IsLastCount=1 then
                                                                      update workflow_operatelog set IsLast=0 where tableid='{0}' and recordid='{1}' and islast=1;
                                                                      end if;
                                                              end;", tableID, recordID);
                    DBHelper.ExecuteScalar(strSql);
                }
                else
                {
                    throw new Exception("此系统只支持oracle");
                }
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// 签收数据
        /// </summary>
        /// <param name="signUserID"></param>
        /// <param name="operateLogID"></param>
        /// <returns></returns>
        public string SignData(Guid signUserID, int tableID, string recordID)
        {
            string rtnStr = string.Empty;
            try
            {
                if (DBHelper.database is Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase)
                {
                    //string strSqlSign = string.Format(@"update workflow_operatelog set signuserid='{0}',signtime=sysdate where touserid='{0}' and islast=1 and tableid='{1}' and recordid='{2}'", signUserID.ToString(), tableID, recordID);
                    //string strSql = string.Format("update workflow_operatelog set islast=0 where tableid='{1}' and recordid='{2}' and touserid<>'{0}' and signuserid is null", signUserID.ToString(), tableID, recordID);
                    //DBHelper.ExecuteSql(strSqlSign);
                    //DBHelper.ExecuteSql(strSql);
                    string strSqlSign = string.Format(@"declare isLastCount number;
begin
       select count(*) into isLastCount from workflow_operatelog where touserid='{0}' and islast=1 and tableid='{1}' and recordid='{2}';
    if isLastCount>0 then
               update workflow_operatelog set signuserid='{0}',signtime=sysdate where touserid='{0}' and islast=1 and tableid='{1}' and recordid='{2}';
               update workflow_operatelog set islast=0 where tableid='{1}' and recordid='{2}' and touserid<>'{0}' and signuserid is null;
        end if;
  end;", signUserID, tableID, recordID);
                    DBHelper.ExecuteSql(strSqlSign);
                }
                else
                {
                    throw new Exception("此系统只支持oracle");
                }
            }
            catch (Exception ex)
            {
                rtnStr = ex.Message;
            }
            return rtnStr;
        }

        public bool AutoSignData(Guid signUserID, int tableID, string tableName, int statusID)
        {
            try
            {
                string sql = string.Format(@"update workflow_operatelog set signuserid='{0}' , signtime=sysdate , islast=0 where recordid in (select dataid from {1} where statusID={2}) and tableid={3}", signUserID, tableName, statusID, tableID);
                this.DBHelper.ExecuteSql(sql);
                return true;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 获取某记录的流程日志
        /// </summary>
        /// <param name="tableID"></param>
        /// <param name="recordID"></param>
        /// <param name="rtnDataTable"></param>
        /// <returns></returns>
        public string GetFlowLog(int tableID, Guid recordID, out DataTable rtnDataTable)
        {
            rtnDataTable = null;
            string strSql = string.Empty;
            string rtnStr = string.Empty;
            try
            {
                if (DBHelper.database is Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase)
                {
                    strSql = string.Format(@"select * from workflow_operatelog where tableID={0} and recordID='{1}' order by OperateTime", tableID.ToString(), recordID.ToString());
                    rtnDataTable = DBHelper.ExecuteDataSet(strSql).Tables[0];
                }
                else
                {
                    throw new Exception("此系统只支持oracle");
                }
            }
            catch (Exception ex)
            {
                rtnStr = ex.Message;
            }
            return rtnStr;
        }

        /// <summary>
        /// 获取流程所在的操作日志
        /// </summary>
        /// <param name="tableID"></param>
        /// <param name="recordID"></param>
        /// <param name="rtnDataTable"></param>
        /// <returns></returns>
        public string GetAllFlowLog(int tableID, string recordID, out DataTable rtnDataTable)
        {
            rtnDataTable = null;
            string strSql = string.Empty;
            string rtnStr = string.Empty;
            try
            {
                if (DBHelper.database is Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase)
                {
                    strSql = string.Format(@"select * from workflow_operatelog where tableID='{0}' and recordID='{1}'", tableID.ToString(), recordID.ToString());
                    rtnDataTable = DBHelper.ExecuteDataSet(strSql).Tables[0];
                }
                else
                {
                    throw new Exception("此系统只支持oracle");
                }
            }
            catch (Exception ex)
            {
                rtnStr = ex.Message;
            }
            return rtnStr;
        }

        /// <summary>
        /// 取代办
        /// </summary>
        ///<param name="tableName"></param>
        /// <param name="userID"></param>
        /// <param name="beginNum">分页开始数</param>
        /// <param name="count">页大小</param>
        /// <param name="sum">总数</param>
        /// <returns></returns>
        public DataTable GetAgent(ArchiveTableEnum tableEnum, string tableName, string userID, int beginNum, int count, string filter, ref int sum, FilterSortEnum sort = FilterSortEnum.OrderByOperateTimeACS)
        {
            DataTable rtnDataTable = null;
            if (DBHelper.database is Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase)
            {
                string sqlFilter = string.Empty;
                if (!string.IsNullOrEmpty(filter))
                {
                    sqlFilter += " and " + filter;
                }
                string doneAgent = string.Format(@"select * from (select t.*, rownum rn from 
                                                                              (
                                                                              select s.* from {0} s
                                                                              inner join workflow_operatelog l
                                                                              on l.tableid={4} and l.touserid='{1}'
                                                                              and s.DataID=l.recordid
                                                                              and l.islast=1 {5}
                                                                              {6}
                                                                              ) t) e
                                                                            where e.rn between {2} and {3}", tableName, userID, beginNum + 1, beginNum + count, (int)tableEnum, sqlFilter, this.GetFilterSort(tableEnum, sort));
                string countSql = string.Format(@"select count(s.DataID) from {0} s
                                                                              inner join workflow_operatelog l
                                                                              on l.tableid={1} and l.touserid='{2}'
                                                                              and s.DataID=l.recordid
                                                                              and l.islast=1 {3}
                                                                              {4}", tableName, (int)tableEnum, userID, sqlFilter, this.GetFilterSort(tableEnum, sort));
                DataSet oDataSet = DBHelper.ExecuteDataSet(doneAgent);
                if (oDataSet != null && oDataSet.Tables.Count > 0)
                {
                    rtnDataTable = oDataSet.Tables[0];
                }

                object countObj = DBHelper.ExecuteScalar(countSql);
                if (countObj != null)
                {
                    sum = int.Parse(countObj.ToString());
                }
            }
            else
            {
                throw new Exception("此系统只支持oracle");
            }
            return rtnDataTable;
        }

        /// <summary>
        /// 取已办
        /// </summary>
        ///<param name="tableName"></param>
        /// <param name="userID"></param>
        /// <param name="beginNum">分页开始数</param>
        /// <param name="count">页大小</param>
        /// <param name="sum">总数</param>
        /// <returns></returns>
        public DataTable GetDone(ArchiveTableEnum tableEnum, string tableName, string userID, int beginNum, int count, string filter, ref int sum, FilterSortEnum sort = FilterSortEnum.OrderByOperateTimeDESC)
        {
            DataTable rtnDataTable = null;
            if (DBHelper.database is Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase)
            {
                if (!string.IsNullOrEmpty(filter))
                {
                    filter = " and " + filter;
                }

                string doneSql = string.Format(@"select * from (select tem.*,rownum as n from (select s.* from {0} s inner join workflow_operatelog l on s.DataID=l.recordid and l.fromuserid='{3}' and l.TableID='{4}' and l.touserid is not null inner join (select t.DataID,max(t.operatelogid) as realID from (select * from {0} s inner join workflow_operatelog l on s.DataID=l.recordid and l.TableID='{4}' and l.fromuserid='{3}' and l.touserid is not null and l.OperateID>0 {5}) t group by t.DataID) te on te.realID=l.operatelogid  {6}) tem) t where t.n between {1} and {2}", tableName, beginNum + 1, beginNum + count, userID.ToString(), (int)tableEnum, filter, this.GetFilterSort(tableEnum, sort));
                string sumSql = string.Format(@"select count(s.DataID) as n from {0} s inner join workflow_operatelog l on s.DataID=l.recordid and l.fromuserid='{1}' and l.TableID='{2}' and l.touserid is not null inner join (select t.DataID,max(t.operatelogid) as realID from (select * from {0} s inner join workflow_operatelog l on s.DataID=l.recordid and l.TableID='{2}' and l.fromuserid='{1}' and l.touserid is not null and l.OperateID>0 {3}) t group by t.DataID) te on te.realID=l.operatelogid", tableName, userID, (int)tableEnum, filter);
                DataSet oDataSet = DBHelper.ExecuteDataSet(doneSql);
                object objSum = DBHelper.ExecuteScalar(sumSql);
                if (oDataSet != null && oDataSet.Tables.Count > 0 && objSum != null)
                {
                    rtnDataTable = oDataSet.Tables[0];
                    sum = int.Parse(objSum.ToString());
                }
                else
                {
                    throw new Exception("取已办发生异常");
                }
            }
            else
            {
                throw new Exception("此系统只支持oracle");
            }
            return rtnDataTable;
        }

        private string GetFilterSort(ArchiveTableEnum tableEnum, FilterSortEnum sort)
        {
            string rtnStringSort = string.Empty;
            switch (sort)
            {
                case FilterSortEnum.OrderByCreateTimeASC:
                    if (tableEnum == ArchiveTableEnum.SingleWell)
                    {
                        rtnStringSort = "Order by s.InsertTime ASC";
                    }
                    else
                    {
                        rtnStringSort = "Order by s.CreateTime ASC";
                    }
                    break;
                case FilterSortEnum.OrderByCreateTimeDESC:
                    if (tableEnum == ArchiveTableEnum.SingleWell)
                    {
                        rtnStringSort = "Order by s.InsertTime DESC";
                    }
                    else
                    {
                        rtnStringSort = "Order by s.CreateTime DESC";
                    }
                    break;
                case FilterSortEnum.OrderByOperateTimeACS:
                    rtnStringSort = "order by l.operatetime asc";
                    break;
                case FilterSortEnum.OrderByOperateTimeDESC:
                    rtnStringSort = "order by l.operatetime desc";
                    break;
                default:
                    break;
            }

            return rtnStringSort;
        }

        /// <summary>
        /// 获取已经入库的数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="userID"></param>
        /// <param name="beginNum"></param>
        /// <param name="count"></param>
        /// <param name="sum"></param>
        /// <param name="finishStatusID"></param>
        /// <returns></returns>
        public DataTable GetTotal(ArchiveTableEnum tableEnum, string tableName, string userID, int beginNum, int count, out int sum, int finishStatusID, string filter)
        {
            DataTable rtnDataTable = null;
            sum = 0;
            if (this.DBHelper.database is Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase)
            {
                string sqlTotal = string.Empty;
                string sqlCount = string.Empty;
                if (!string.IsNullOrEmpty(userID))
                {
                    if (!string.IsNullOrEmpty(filter))
                    {
                        sqlTotal = string.Format("select * from (select A.*, rownum NumberIndex from (select * from {0} where statusid={1} and {2} and createuserid='{3}') A where rownum <= {4}) b where NumberIndex > {5}", tableName, finishStatusID, filter, userID, beginNum + count, beginNum);
                        sqlCount = string.Format("select count(*) from {0} where statusID={1} and {2} and createuserid='{3}'", tableName, finishStatusID, filter, userID);
                    }
                    else
                    {
                        sqlTotal = string.Format("select * from (select A.*, rownum NumberIndex from (select * from {0} where statusid={1} and createuserid='{2}') A where rownum <= {3}) b where NumberIndex > {4}", tableName, finishStatusID, userID, beginNum + count, beginNum);
                        sqlCount = string.Format("select count(*) from {0} where statusID={1}  and createuserid='{2}'", tableName, finishStatusID, userID);
                    }
                }
                else
                {
                    if (!string.IsNullOrEmpty(filter))
                    {
                        sqlTotal = string.Format("select * from (select A.*, rownum NumberIndex from (select * from {0} where statusid={1} and {2} ) A where rownum <= {3}) b where NumberIndex > {4}", tableName, finishStatusID, filter, beginNum + count, beginNum);
                        sqlCount = string.Format("select count(*) from {0} where statusID={1} and {2}", tableName, finishStatusID, filter);
                    }
                    else
                    {
                        sqlTotal = string.Format("select * from (select A.*, rownum NumberIndex from (select * from {0} where statusid={1} ) A where rownum <= {2}) b where NumberIndex > {3}", tableName, finishStatusID, beginNum + count, beginNum);
                        sqlCount = string.Format("select count(*) from {0} where statusID={1}", tableName, finishStatusID);
                    }
                }

                DataSet oDataSet = this.DBHelper.ExecuteDataSet(sqlTotal);
                if (oDataSet != null && oDataSet.Tables.Count > 0)
                {
                    rtnDataTable = oDataSet.Tables[0];
                }

                object obj = this.DBHelper.ExecuteScalar(sqlCount);
                if (obj != null)
                {
                    sum = int.Parse(obj.ToString());
                }
            }
            else
            {
                throw new Exception("此系统只支持oracle");
            }
            return rtnDataTable;
        }

        public DataTable GetTotalAll(ArchiveTableEnum tableEnum, string tableName, string userID, int finishStatusID, string filter)
        {
            DataTable rtnDataTable = null;
            if (this.DBHelper.database is Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase)
            {
                string sqlTotal = string.Empty;
                string sqlCount = string.Empty;
                if (string.IsNullOrEmpty(userID))
                {
                    if (!string.IsNullOrEmpty(filter))
                    {
                        sqlTotal = string.Format("select * from {0} where statusID ={1} and {2}", tableName, finishStatusID, filter);
                    }
                    else
                    {
                        sqlTotal = string.Format("select * from {0} where statusID ={1}", tableName, finishStatusID);
                    }
                }
                else
                {
                    //先不实现，设计到是否查询某用户的数据
                }

                DataSet oDataSet = this.DBHelper.ExecuteDataSet(sqlTotal);
                if (oDataSet != null && oDataSet.Tables.Count > 0)
                {
                    rtnDataTable = oDataSet.Tables[0];
                }
            }
            else
            {
                throw new Exception("此系统只支持oracle");
            }
            return rtnDataTable;
        }

        /// <summary>
        /// 获取角色下的所有用户
        /// </summary>
        /// <param name="roleID"></param>
        /// <returns></returns>
        public DataTable GetUsersByRoleID(string roleID)
        {
            string sql = "select ur.role_id,u.User_ID,u.User_Name  from p_auth_user u ,p_auth_user_role ur where ur.user_id=u.user_id and ur.role_id='" + roleID + "'";
            return DBHelper.ExecuteDataSet(sql).Tables[0];
        }

        /// <summary>
        /// 获取角色下、单位下所有用户
        /// </summary>
        /// <param name="roleID"></param>
        /// <param name="orgID"></param>
        /// <returns></returns>
        public DataTable GetUsersByRoleIDAndOrgID(string roleID, string orgID)
        {
            return null;
        }
    }
}